package com.lqz.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.tnc.model.AllDataBean;

public class SqlHelper {
	private static Connection ct=null;
	private static ResultSet rs=null;
	private static PreparedStatement ps=null;
	private static Statement cs;
	public static void main(String [] a){
//		String sqlString="select * from users ";
//		ArrayList<Object> rrs = executeSql(sqlString);
//		for(int i=0;i<rrs.size();i++){
//			Object[] object=(Object[]) rrs.get(i);
//			for(int j=0;j<object.length;j++){
//				System.out.println(object[j]);	
//			}
//		}
		
	}
	
	public static int insertData(String sql,String []paras){
		int flagint = 0;
		try{
			ct=DBSql2008con.getCon();
			ct.setAutoCommit(false);
			ct.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			ps=ct.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
				//System.out.println(paras[i]);
			}
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if(rs.next()) {
				flagint=rs.getInt(1);
	         } 
			ct.commit();
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return flagint;
	}
	public static ArrayList<Object> quertyPage(String sql,int []paras){
		ArrayList<Object> a1=new ArrayList<Object>();
		try{
			ct=DBSql2008con.getCon();
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setInt(i+1,paras[i]);
			}
			rs=ps.executeQuery();
			ResultSetMetaData rsmd=rs.getMetaData();
			//通过rsmd获取该结果集有多少列
			int columnNum=rsmd.getColumnCount();
			//循环取出数据，并封装到arraylist中
			while (rs.next()) {
				Object[] objects=new Object[columnNum];
				for(int i=0;i<objects.length;i++){
					objects[i]=rs.getObject(i+1);
				}
				a1.add(objects);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}
	
	public static ArrayList<Object> executeSql(String sql) {
		ArrayList<Object> a1=new ArrayList<Object>();
		try{
			ct=DBSql2008con.getCon();
			cs=ct.createStatement();
			rs=cs.executeQuery(sql);
			ResultSetMetaData rsmd=rs.getMetaData();
			//通过rsmd获取该结果集有多少列
			int columnNum=rsmd.getColumnCount();
			//循环取出数据，并封装到arraylist中
			while (rs.next()) {
				
				Object[] objects=new Object[columnNum];
				for(int i=0;i<objects.length;i++){
					objects[i]=rs.getObject(i+1);
				}
				a1.add(objects);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}
	public static ArrayList<Object> executeQuerty(String sql,String []paras){
		ArrayList<Object> a1=new ArrayList<Object>();
		try{
			ct=DBSql2008con.getCon();
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
			}
			rs=ps.executeQuery();
			
			ResultSetMetaData rsmd=rs.getMetaData();
			//通过rsmd获取该结果集有多少列
			int columnNum=rsmd.getColumnCount();
			//循环取出数据，并封装到arraylist中
			while (rs.next()) {
				Object[] objects=new Object[columnNum];
				for(int i=0;i<objects.length;i++){
					objects[i]=rs.getObject(i+1);
				}
				a1.add(objects);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}
	
	public static int  deleteData(String sql,String []paras) throws NamingException, SQLException{
		ct=DBSql2008con.getCon();
		int t=0;
		try {
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
			}
			t=ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return t;
	}
	//用于更新数据
	public static String update(String sql,String []paras) throws NamingException, SQLException{
		String reString = "连接数据库对象失败";
		ct=DBSql2008con.getCon();
		int t = 0;
		try {
			ps=ct.prepareStatement(sql);
		} catch (SQLException e) {
				// TODO Auto-generated catch block
//			e.printStackTrace();
			reString = "连接数据库失败";
		}
			//给sql的问号赋值
		for(int i=0;i<paras.length;i++){
			try {
				ps.setString (i+1,paras[i]);
			} catch (SQLException e) {
					// TODO Auto-generated catch block
//				e.printStackTrace();
				reString = "此记录不存在";
			}
				
		}
			try {
				t=ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				reString = "数据库执行出错，已经存在此条记录！";
			}
			//System.out.println(t);
		DBSql2008con.closecon(ct,ps,rs,cs);
		if(t!=0){
			reString = "true";
		}
		return reString;
	}

	public static int insert(String sql) throws NamingException, SQLException {
		// TODO Auto-generated method stub
		ct=DBSql2008con.getCon();
		int count = 0;
		 try {
			 cs = ct.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		 try {
			count = cs.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		 DBSql2008con.closecon(ct,ps,rs,cs);
		return count;
	}
	public static <T> List<T> queryGetBeanNA(String sql,String []paras,T object){
		List<T> a1=new ArrayList<T>();
		try{
			ct=DBSql2008con.getCon();
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
			}
			
			rs=ps.executeQuery();
//			ResultSetMetaData rsmd=rs.getMetaData();
//			int columnNum=rsmd.getColumnCount();
//			//获取列名
//			for(int i = 0 ; i < columnNum ; i++){ 
//				System.out.println( rsmd.getColumnName(i+1).toString()); 
//			}
			if(!rs.wasNull()){
				a1=CustomClass.rs2BeanNA(rs, object);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}
	public static <T> List<T> queryGetBean(String sql,String []paras,T object){
		List<T> a1=new ArrayList<T>();
		try{
			ct=DBSql2008con.getCon();
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
			}
			rs=ps.executeQuery();
			if(!rs.wasNull()){
				a1=CustomClass.rs2Bean(rs, object);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}
	public static int[] insertDatas(ArrayList<String> arraySql) throws NamingException, SQLException {
		// TODO Auto-generated method stub
		/***
		 * 功能：执行批处理sql语句
		 * paras ： sql语句组成的arraylist
		 * return ：执行成功后的数目
		 */
		int[] n = null;
		ct=DBSql2008con.getCon();
		Statement stmt = null;
		 try {
			stmt = ct.createStatement();
			for (String sql:arraySql) {
				stmt.addBatch(sql);
			}
			n=stmt.executeBatch(); 
			DBSql2008con.closecon(ct,ps,rs,cs);
		 } catch (SQLException e) {
				// TODO Auto-generated catch block
			 
			 //e.printStackTrace();
			} 
		return n;
	}
	public static ArrayList<String[]> execSqlgetFiled(String sql) {
		ArrayList<String[]> a1=new ArrayList<String[]>();
		try{
			ct=DBSql2008con.getCon();
			cs=ct.createStatement();
			rs=cs.executeQuery(sql);
			ResultSetMetaData rsmd=rs.getMetaData();
			//通过rsmd获取该结果集有多少列
			int columnNum=rsmd.getColumnCount();
			//获取列名
			String[] fieldName=new String[columnNum];
			for(int i = 0 ; i < columnNum ; i++){ 
				fieldName[i] = rsmd.getColumnName(i+1).toString(); 
			}
			a1.add(fieldName);
			//循环取出数据，并封装到arraylist中
			while (rs.next()) {
				String[] objects=new String[columnNum];
				for(int i=0;i<objects.length;i++){
					objects[i]=rs.getObject(i+1)+"";
				}
				a1.add(objects);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return a1;
	}

	public static int queryGetBeanNACount(String sql, String[] paras, AllDataBean bean) {
		// TODO Auto-generated method stub
		int count=0;
		try{
			ct=DBSql2008con.getCon();
			ps=ct.prepareStatement(sql);
			//给sql的问号赋值
			for(int i=0;i<paras.length;i++){
				ps.setString (i+1,paras[i]);
			}
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
	         } 
			
		}catch (Exception e) {
			e.printStackTrace();
		}
		DBSql2008con.closecon(ct,ps,rs,cs);
		return count;
	}
	
}








